package com.cn.ccp.service.system;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.MenuManageBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.MsgConstants;

public class MenuManageService extends BasicService {
	
	private Logger log = Logger.getLogger(MenuManageService.class);
	
	public boolean delete(String userid){
		String sql = "DELETE FROM role_user WHERE user_id = ? ";
		return jdbcTemplate.update(sql, new Object[]{userid}) > 0;
	}
	
	public List<String> getUserRoleIdList(String userid){
		String sql = "SELECT role_id FROM role_user WHERE user_id = ?";
		return jdbcTemplate.queryForList(sql,new Object[]{userid} ,String.class);
	}
	
	public boolean insertUserRoleByCourse(final String roleid ,final List<String> userlist){
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "DELETE FROM role_user WHERE role_id = ?";
					jdbcTemplate.update(sql, new Object[]{roleid}) ;
					for (int i = 0; i < userlist.size(); i++) {
						sql = "insert into role_user(user_id ,role_id) values (?,?)";
						jdbcTemplate.update(sql ,new Object[]{userlist.get(i) , roleid});
					}
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}
	
	public boolean insertUserRole(final String userid ,final String selectRoleStr){
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					delete(userid);
					String sql = "insert into role_user(user_id ,role_id) values (?,?)";
					String[] strs = selectRoleStr.split(",");
					for(String s : strs)
						jdbcTemplate.update(sql , new Object[]{userid ,s});
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}
	
	public int getCount(String userNumber ,String userName ,String userType ,String roleid , String facultyid){
		String sql = "SELECT count(distinct(u.id)) "+ 
					" FROM  USER u  "+
					" LEFT JOIN role_user ru  ON ru.user_id = u.id "+ 
					" LEFT JOIN role r ON r.id = ru.role_id "+
					" LEFT JOIN faculty f ON f.id = u.faculty_id ";
		StringBuffer sb = new StringBuffer();
		if(userNumber != null  && !userNumber.equals("") && !userNumber.equals("null")){
			sb.append(" u.number = '"+userNumber+"' ");
		}
		if(userName != null  && !userName.equals("") && !userName.equals("null")){
			if(sb.length() == 0)
				sb.append(" u.username = '"+userName+"' ");
			else
				sb.append(" and u.username = '"+userName+"' ");
		}
		if(userType != null  && !userType.equals("") && !userType.equals("null") && !userType.equals("-1")){
			if(sb.length() == 0)
				sb.append(" u.user_type = "+userType);
			else
				sb.append(" and u.user_type = "+userType);
		}
		if(roleid != null  && !roleid.equals("") && !roleid.equals("null") && !roleid.equals("-1")){
			if(sb.length() == 0)
				sb.append(" r.id = "+roleid);
			else
				sb.append(" and r.id = "+roleid);
		}
		if(facultyid != null  && !facultyid.equals("") && !facultyid.equals("null") && !facultyid.equals("-1")){
			if(sb.length() == 0)
				sb.append(" f.id = "+facultyid);
			else
				sb.append(" and f.id = "+facultyid);
		}
		if(sb.length() > 0 )
			sql = sql + " where " +sb.toString() +" ";
		try {
			log.info(sql);
			return jdbcTemplate.queryForInt(sql);
		} catch (Exception e) {
			return 0 ;
		}
	}
	
	public List<MenuManageBean> getAll(int start ,int page ,String userNumber ,String userName ,String userType ,String roleid , String facultyid){
		String sql = "select u.id, u.number ,u.username ,f.facultyname , GROUP_CONCAT(distinct r.rolename ) as rolename,"+ 
							"u.user_type ,u.status ,u.mobile ,u.email,s.specialtyname,GROUP_CONCAT(distinct tg.teaching_group_name ) as groupname " + 
							" FROM  USER u  "+
							" left outer join specialty  s on s.id = u.specialty_id "+ 
							" left outer join faculty f ON f.id = u.faculty_id "+
							" left join role_user ru on ru.user_id = u.id" + 
							" left outer join role r on r.id = ru.role_id  "+
							" left join user_teaching_group utg on utg.user_id = u.id " +
							" left outer join teaching_group tg on tg.id = utg.teaching_group_id ";
		StringBuffer sb = new StringBuffer();
		if(userNumber != null  && !userNumber.equals("") && !userNumber.equals("null")){
			sb.append(" u.number = '"+userNumber+"' ");
		}
		if(userName != null  && !userName.equals("") && !userName.equals("null")){
			if(sb.length() == 0)
				sb.append(" u.username = '"+userName+"' ");
			else
				sb.append(" and u.username = '"+userName+"' ");
		}
		if(userType != null  && !userType.equals("") && !userType.equals("null") && !userType.equals("-1")){
			if(sb.length() == 0)
				sb.append(" u.user_type = "+userType);
			else
				sb.append(" and u.user_type = "+userType);
		}
		if(roleid != null  && !roleid.equals("") && !roleid.equals("null") && !roleid.equals("-1")){
			if(sb.length() == 0)
				sb.append(" r.id = "+roleid);
			else
				sb.append(" and r.id = "+roleid);
		}
		if(facultyid != null  && !facultyid.equals("") && !facultyid.equals("null") && !facultyid.equals("-1")){
			if(sb.length() == 0)
				sb.append(" f.id = "+facultyid);
			else
				sb.append(" and f.id = "+facultyid);
		}
		if(sb.length() > 0 )
			sql = sql + " where " +sb.toString();
		sql = sql + " GROUP BY u.id limit ?,?";
		log.info(sql);
		return jdbcTemplate.query(sql, new Object[]{start , page} , new MenuManageMapper());
	}
	
	public class MenuManageMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			MenuManageBean bean = new MenuManageBean();
			bean.setUserId(rs.getString("id"));
			bean.setUserNumber(rs.getString("number"));
			bean.setUserName(rs.getString("username"));
			bean.setFacultyName(rs.getString("facultyname"));
			bean.setRoleName(rs.getString("rolename"));
			String userType = rs.getString("user_type");
			if(userType.equals("1")){
				bean.setUserType(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_STUDENT));
			}else if(userType.equals("2")){
				bean.setUserType(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_FULL_TIME_TEACHER));
			}else if(userType.equals("3")){
				bean.setUserType(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_PART_TIME_TEACHER));
			}else if(userType.equals("4")){
				bean.setUserType(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_EXT_TEACHER));
			}
			 
			String status = rs.getString("status");
			if(status.equals("0")){
				bean.setUserState(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_OK));
			}else if(status.equals("-1")){
				bean.setUserState(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_DELETE));
			}else if(status.equals("-2")){
				bean.setUserState(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_NO));
			}else if(status.equals("1")){
				bean.setUserState(BaseAction.getInstance().getMessages("user_page_label_status_release_course"));
			}
			bean.setMobile(rs.getString("mobile"));
			bean.setEmail(rs.getString("email"));
			try{
				bean.setSpecialtyname(rs.getString("specialtyname"));
			}catch(Exception e){}
			try{
				if(rs.getString("groupname").length() >=10 )
					bean.setTeachGroup(rs.getString("groupname").substring(0, 9));
				else
					bean.setTeachGroup(rs.getString("groupname"));
				bean.setTeachGroupTitle(rs.getString("groupname"));
			}catch(Exception e){}
			return bean ;
		}
	}
}
































